{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from pandas import Series, DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>State.Code</th>\n",
       "      <th>Total.Math</th>\n",
       "      <th>Family Income.Between 20-40k.Math</th>\n",
       "      <th>Family Income.Between 40-60k.Math</th>\n",
       "      <th>Family Income.Between 60-80k.Math</th>\n",
       "      <th>Family Income.Between 80-100k.Math</th>\n",
       "      <th>Family Income.Less than 20k.Math</th>\n",
       "      <th>Family Income.More than 100k.Math</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2005</td>\n",
       "      <td>AL</td>\n",
       "      <td>559</td>\n",
       "      <td>513</td>\n",
       "      <td>539</td>\n",
       "      <td>550</td>\n",
       "      <td>566</td>\n",
       "      <td>462</td>\n",
       "      <td>588</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2005</td>\n",
       "      <td>AK</td>\n",
       "      <td>519</td>\n",
       "      <td>492</td>\n",
       "      <td>517</td>\n",
       "      <td>513</td>\n",
       "      <td>528</td>\n",
       "      <td>464</td>\n",
       "      <td>541</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2005</td>\n",
       "      <td>AZ</td>\n",
       "      <td>530</td>\n",
       "      <td>498</td>\n",
       "      <td>520</td>\n",
       "      <td>524</td>\n",
       "      <td>534</td>\n",
       "      <td>485</td>\n",
       "      <td>554</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2005</td>\n",
       "      <td>AR</td>\n",
       "      <td>552</td>\n",
       "      <td>513</td>\n",
       "      <td>543</td>\n",
       "      <td>553</td>\n",
       "      <td>570</td>\n",
       "      <td>489</td>\n",
       "      <td>572</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2005</td>\n",
       "      <td>CA</td>\n",
       "      <td>522</td>\n",
       "      <td>477</td>\n",
       "      <td>506</td>\n",
       "      <td>521</td>\n",
       "      <td>535</td>\n",
       "      <td>451</td>\n",
       "      <td>566</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year State.Code  Total.Math  Family Income.Between 20-40k.Math  \\\n",
       "0  2005         AL         559                                513   \n",
       "1  2005         AK         519                                492   \n",
       "2  2005         AZ         530                                498   \n",
       "3  2005         AR         552                                513   \n",
       "4  2005         CA         522                                477   \n",
       "\n",
       "   Family Income.Between 40-60k.Math  Family Income.Between 60-80k.Math  \\\n",
       "0                                539                                550   \n",
       "1                                517                                513   \n",
       "2                                520                                524   \n",
       "3                                543                                553   \n",
       "4                                506                                521   \n",
       "\n",
       "   Family Income.Between 80-100k.Math  Family Income.Less than 20k.Math  \\\n",
       "0                                 566                               462   \n",
       "1                                 528                               464   \n",
       "2                                 534                               485   \n",
       "3                                 570                               489   \n",
       "4                                 535                               451   \n",
       "\n",
       "   Family Income.More than 100k.Math  \n",
       "0                                588  \n",
       "1                                541  \n",
       "2                                554  \n",
       "3                                572  \n",
       "4                                566  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filename = '../data/sat-scores.csv'\n",
    "\n",
    "df = pd.read_csv(filename,\n",
    "                usecols=['Year', 'State.Code', 'Total.Math', \n",
    "                         'Family Income.Less than 20k.Math', \n",
    "                         'Family Income.Between 20-40k.Math', \n",
    "                         'Family Income.Between 40-60k.Math', \n",
    "                         'Family Income.Between 60-80k.Math',\n",
    "                         'Family Income.Between 80-100k.Math',\n",
    "                         'Family Income.More than 100k.Math'])\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Rename the income-related column names\n",
    "df = df.rename(columns={'Family Income.Less than 20k.Math':'income<20k',\n",
    "                'Family Income.Between 20-40k.Math':'20k<income<40k',\n",
    "                'Family Income.Between 40-60k.Math':'40k<income<60k',\n",
    "                'Family Income.Between 60-80k.Math':'60k<income<80k',\n",
    "                'Family Income.Between 80-100k.Math':'80k<income<100k',\n",
    "                'Family Income.More than 100k.Math':'income>100k'})\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Beyond 1\n",
    "\n",
    "Calculate descriptive statistics for all of the changes in income brackets.  Where do we see the largest difference between income brackets?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>income&lt;20k</th>\n",
       "      <th>20k&lt;income&lt;40k</th>\n",
       "      <th>40k&lt;income&lt;60k</th>\n",
       "      <th>60k&lt;income&lt;80k</th>\n",
       "      <th>80k&lt;income&lt;100k</th>\n",
       "      <th>income&gt;100k</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>0.0</td>\n",
       "      <td>11.000000</td>\n",
       "      <td>11.000000</td>\n",
       "      <td>11.000000</td>\n",
       "      <td>11.000000</td>\n",
       "      <td>11.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.083929</td>\n",
       "      <td>0.045260</td>\n",
       "      <td>0.020744</td>\n",
       "      <td>0.025247</td>\n",
       "      <td>0.034399</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.026723</td>\n",
       "      <td>0.009055</td>\n",
       "      <td>0.008745</td>\n",
       "      <td>0.004821</td>\n",
       "      <td>0.008947</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.044260</td>\n",
       "      <td>0.034768</td>\n",
       "      <td>0.003136</td>\n",
       "      <td>0.015391</td>\n",
       "      <td>0.012418</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.073665</td>\n",
       "      <td>0.041450</td>\n",
       "      <td>0.019374</td>\n",
       "      <td>0.023645</td>\n",
       "      <td>0.035291</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.083207</td>\n",
       "      <td>0.043872</td>\n",
       "      <td>0.023743</td>\n",
       "      <td>0.024947</td>\n",
       "      <td>0.036175</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.094456</td>\n",
       "      <td>0.045532</td>\n",
       "      <td>0.026016</td>\n",
       "      <td>0.028681</td>\n",
       "      <td>0.039508</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.142793</td>\n",
       "      <td>0.069618</td>\n",
       "      <td>0.029694</td>\n",
       "      <td>0.032817</td>\n",
       "      <td>0.042319</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       income<20k  20k<income<40k  40k<income<60k  60k<income<80k  \\\n",
       "count         0.0       11.000000       11.000000       11.000000   \n",
       "mean          NaN        0.083929        0.045260        0.020744   \n",
       "std           NaN        0.026723        0.009055        0.008745   \n",
       "min           NaN        0.044260        0.034768        0.003136   \n",
       "25%           NaN        0.073665        0.041450        0.019374   \n",
       "50%           NaN        0.083207        0.043872        0.023743   \n",
       "75%           NaN        0.094456        0.045532        0.026016   \n",
       "max           NaN        0.142793        0.069618        0.029694   \n",
       "\n",
       "       80k<income<100k  income>100k  \n",
       "count        11.000000    11.000000  \n",
       "mean          0.025247     0.034399  \n",
       "std           0.004821     0.008947  \n",
       "min           0.015391     0.012418  \n",
       "25%           0.023645     0.035291  \n",
       "50%           0.024947     0.036175  \n",
       "75%           0.028681     0.039508  \n",
       "max           0.032817     0.042319  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "change = df.groupby('Year')[['income<20k',\n",
    "                      '20k<income<40k',\n",
    "                      '40k<income<60k',\n",
    "                      '60k<income<80k',\n",
    "                      '80k<income<100k',\n",
    "                      'income>100k']].mean().T.pct_change() \n",
    "\n",
    "# largest is for the wealthiest students, whose average scores are \n",
    "# far higher than any other income bracket.\n",
    "\n",
    "change.T.describe() "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Beyond 2\n",
    "\n",
    "Which five states have the greatest gap in SAT math scores between the richest and poorest students?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "State.Code\n",
       "ND    341.909091\n",
       "WY    246.454545\n",
       "DC    208.818182\n",
       "SD    157.000000\n",
       "MS    140.000000\n",
       "Name: rich_poor_diff, dtype: float64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['rich_poor_diff'] = df['income>100k'] - df['income<20k']\n",
    "\n",
    "df.groupby('State.Code')['rich_poor_diff'].mean().sort_values(ascending=False).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Beyond 3\n",
    "\n",
    "We analyzed math scores. If we perform the same analysis on verbal SAT scores, will we similarly see that wealthier students generally do better than poorer students?  Are there any income brackets that do worse than the next-poorer bracket?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>State.Code</th>\n",
       "      <th>Total.Verbal</th>\n",
       "      <th>income&lt;20k</th>\n",
       "      <th>20k&lt;income&lt;40k</th>\n",
       "      <th>40k&lt;income&lt;60k</th>\n",
       "      <th>60k&lt;income&lt;80k</th>\n",
       "      <th>80k&lt;income&lt;100k</th>\n",
       "      <th>income&gt;100k</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2005</td>\n",
       "      <td>AL</td>\n",
       "      <td>567</td>\n",
       "      <td>527</td>\n",
       "      <td>551</td>\n",
       "      <td>564</td>\n",
       "      <td>577</td>\n",
       "      <td>474</td>\n",
       "      <td>590</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2005</td>\n",
       "      <td>AK</td>\n",
       "      <td>523</td>\n",
       "      <td>500</td>\n",
       "      <td>522</td>\n",
       "      <td>519</td>\n",
       "      <td>534</td>\n",
       "      <td>467</td>\n",
       "      <td>544</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2005</td>\n",
       "      <td>AZ</td>\n",
       "      <td>526</td>\n",
       "      <td>495</td>\n",
       "      <td>518</td>\n",
       "      <td>523</td>\n",
       "      <td>533</td>\n",
       "      <td>474</td>\n",
       "      <td>546</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2005</td>\n",
       "      <td>AR</td>\n",
       "      <td>563</td>\n",
       "      <td>526</td>\n",
       "      <td>555</td>\n",
       "      <td>570</td>\n",
       "      <td>580</td>\n",
       "      <td>486</td>\n",
       "      <td>589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2005</td>\n",
       "      <td>CA</td>\n",
       "      <td>504</td>\n",
       "      <td>458</td>\n",
       "      <td>494</td>\n",
       "      <td>511</td>\n",
       "      <td>525</td>\n",
       "      <td>421</td>\n",
       "      <td>551</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year State.Code  Total.Verbal  income<20k  20k<income<40k  40k<income<60k  \\\n",
       "0  2005         AL           567         527             551             564   \n",
       "1  2005         AK           523         500             522             519   \n",
       "2  2005         AZ           526         495             518             523   \n",
       "3  2005         AR           563         526             555             570   \n",
       "4  2005         CA           504         458             494             511   \n",
       "\n",
       "   60k<income<80k  80k<income<100k  income>100k  \n",
       "0             577              474          590  \n",
       "1             534              467          544  \n",
       "2             533              474          546  \n",
       "3             580              486          589  \n",
       "4             525              421          551  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filename = '../data/sat-scores.csv'\n",
    "\n",
    "df = pd.read_csv(filename,\n",
    "                usecols=['Year', 'State.Code', 'Total.Verbal', \n",
    "                         'Family Income.Less than 20k.Verbal', \n",
    "                         'Family Income.Between 20-40k.Verbal', \n",
    "                         'Family Income.Between 40-60k.Verbal', \n",
    "                         'Family Income.Between 60-80k.Verbal',\n",
    "                         'Family Income.Between 80-100k.Verbal',\n",
    "                         'Family Income.More than 100k.Verbal'])\n",
    "\n",
    "df.columns = ['Year', 'State.Code', 'Total.Verbal',\n",
    "                      'income<20k',\n",
    "                      '20k<income<40k',\n",
    "                      '40k<income<60k',\n",
    "                      '60k<income<80k',\n",
    "                      '80k<income<100k',\n",
    "                      'income>100k',\n",
    "                      ]\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>Year</th>\n",
       "      <th>2005</th>\n",
       "      <th>2006</th>\n",
       "      <th>2007</th>\n",
       "      <th>2008</th>\n",
       "      <th>2009</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>80k&lt;income&lt;100k</th>\n",
       "      <td>-0.165165</td>\n",
       "      <td>-0.16959</td>\n",
       "      <td>-0.166568</td>\n",
       "      <td>-0.141209</td>\n",
       "      <td>-0.152243</td>\n",
       "      <td>-0.143227</td>\n",
       "      <td>-0.160203</td>\n",
       "      <td>-0.15622</td>\n",
       "      <td>-0.13885</td>\n",
       "      <td>-0.162701</td>\n",
       "      <td>-0.18397</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Year                 2005     2006      2007      2008      2009      2010  \\\n",
       "80k<income<100k -0.165165 -0.16959 -0.166568 -0.141209 -0.152243 -0.143227   \n",
       "\n",
       "Year                 2011     2012     2013      2014     2015  \n",
       "80k<income<100k -0.160203 -0.15622 -0.13885 -0.162701 -0.18397  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "change = df.groupby('Year')[['income<20k',\n",
    "                      '20k<income<40k',\n",
    "                      '40k<income<60k',\n",
    "                      '60k<income<80k',\n",
    "                      '80k<income<100k',\n",
    "                      'income>100k']].mean().T.pct_change() \n",
    "\n",
    "change[change <= 0].dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
